DROP USER CPS CASCADE;
DROP USER CPS_APP CASCADE;
DROP ROLE CPS_UPDATE_ROLE CASCADE;
DROP ROLE CPS_RO_ROLE CASCADE;
DROP TABLESPACE CPS_DATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE CPS_INDEX INCLUDING CONTENTS AND DATAFILES;
COMMIT;

CREATE TABLESPACE CPS_DATA
  LOGGING
  DATAFILE 'C:\ReMan\cps_data.dbf' 
  SIZE 20M 
  AUTOEXTEND ON
  NEXT 10M MAXSIZE 2G;
  COMMIT;
  
  CREATE TABLESPACE CPS_INDEX
  LOGGING
  DATAFILE 'C:\ReMan\cps_index.dbf' 
  SIZE 20M 
  AUTOEXTEND ON
  NEXT 10M MAXSIZE 2G;
  COMMIT;

CREATE ROLE CPS_UPDATE_ROLE;
CREATE ROLE CPS_RO_ROLE;



CREATE USER cps
IDENTIFIED BY VALUES 'cps'
DEFAULT TABLESPACE CPS_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
COMMIT;

ALTER USER cps QUOTA UNLIMITED ON CPS_DATA;
ALTER USER cps QUOTA UNLIMITED ON CPS_INDEX;
ALTER USER cps IDENTIFIED by cps;
GRANT RESOURCE TO cps;
GRANT CONNECT TO cps;
GRANT SELECT_CATALOG_ROLE TO cps;
GRANT CPS_UPDATE_ROLE TO cps;
GRANT CREATE VIEW TO cps;
GRANT CREATE PUBLIC SYNONYM TO cps;
GRANT CREATE SESSION TO cps;
ALTER USER cps DEFAULT ROLE ALL;
COMMIT;

CREATE USER cps_app
IDENTIFIED BY VALUES 'cpsapp'
DEFAULT TABLESPACE CPS_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
COMMIT;

ALTER USER cps_app QUOTA UNLIMITED ON CPS_DATA;
ALTER USER cps_app QUOTA UNLIMITED ON CPS_INDEX;
ALTER USER cps_app IDENTIFIED by cpsapp;
GRANT RESOURCE TO cps_app;
GRANT CONNECT TO cps_app;
GRANT CPS_UPDATE_ROLE TO cps_app;
GRANT SELECT_CATALOG_ROLE TO cps_app;
ALTER USER cps_app DEFAULT ROLE ALL;
COMMIT;

 --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 CREATE TABLE CPS.ADDRESS
  (
    
  ADDRESS_ID NUMBER(16) NOT NULL ,
  ADDRESS_LINE1 VARCHAR2(50) , 
  ADDRESS_LINE2 VARCHAR2(50) , 
  CITY VARCHAR2(20) , 
  STATE VARCHAR2(20) , 
  ZIPCODE VARCHAR2(20) , 
  COUNTRY VARCHAR2(20),
  ADDRESS_TYPE VARCHAR2(200),
  CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
,  CONSTRAINT ADDRESS_ID_PK PRIMARY KEY (  ADDRESS_ID  ) ENABLE 
  )
 /* SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.ADDRESS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.ADDRESS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ADDRESS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.ADDRESS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ADDRESS TO CPS_RO_ROLE;
COMMIT;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE CPS.ENTERPRISE 
(
  ENTERPRISE_ID NUMBER(16) NOT NULL 
, ENTERPRISE_NAME VARCHAR2(50) 
, STATUS NUMBER
, CONTACT_NAME VARCHAR2(50) 
, CONTACT_PHONE_NUMBER VARCHAR2(20) 
, CONTACT_EMAIL VARCHAR2(20) 
, ADDRESS_ID NUMBER(16) 
, LICENSE_TYPE NUMBER(2) 
, LICENSE_KEY VARCHAR2(50) 
, LICENSE_EXPIRY_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT ENTERPRIS_ID_PK PRIMARY KEY   (    ENTERPRISE_ID  )   ENABLE 
)
 /* SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.ENTERPRISE ADD CONSTRAINT ENTERPRISE_ADDRESS_ID_FK FOREIGN KEY (ADDRESS_ID) REFERENCES CPS.ADDRESS (ADDRESS_ID);
GRANT DELETE		ON CPS.ENTERPRISE TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.ENTERPRISE TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ENTERPRISE TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.ENTERPRISE TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ENTERPRISE TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

CREATE TABLE CPS.DEPARTMENTS 
(
  DEPARTMENT_ID NUMBER(16) NOT NULL 
, DEPARTMENT_NAME VARCHAR2(50) 
, DEPARTMENT_CODE VARCHAR2(20) 
, ADDRESS_ID NUMBER(16) 
, ENTERPRISE_ID NUMBER(16)
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY   (    DEPARTMENT_ID  )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
 ALTER TABLE CPS.DEPARTMENTS ADD CONSTRAINT DEPARTMENTS_ADDRESS_ID_FK FOREIGN KEY (ADDRESS_ID) REFERENCES CPS.ADDRESS (ADDRESS_ID); 
 ALTER TABLE CPS.DEPARTMENTS ADD CONSTRAINT DEPARTMENTS_ENTERPRISE_ID_FK FOREIGN KEY (ENTERPRISE_ID) REFERENCES CPS.ENTERPRISE (ENTERPRISE_ID); 
  
GRANT DELETE		ON CPS.DEPARTMENTS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.DEPARTMENTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.DEPARTMENTS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.DEPARTMENTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.DEPARTMENTS TO CPS_RO_ROLE;
COMMIT;
 --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 CREATE TABLE CPS.PRIVILEGE_DETAILS
(
  PRIVILEGE_DETAILS_ID NUMBER(16) NOT NULL 
, PREVILEGE_NAME VARCHAR2(20) 
, DESCRIPTION VARCHAR2(100) 
, CREATED_BY NUMBER(16)
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, IS_ALLOWED CHAR(1)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT PRIVILEGE_DETAILS_ID_PK PRIMARY KEY   (    PRIVILEGE_DETAILS_ID   )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.PRIVILEGE_DETAILS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.PRIVILEGE_DETAILS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.PRIVILEGE_DETAILS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.PRIVILEGE_DETAILS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.PRIVILEGE_DETAILS TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.ROLES
(
  ROLE_ID NUMBER(16) NOT NULL 
, ROLE_NAME VARCHAR2(20) 
, DESCRIPTION VARCHAR2(100) 
, CREATED_BY NUMBER(16)
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT ROLE_ID_PK PRIMARY KEY   (    ROLE_ID  )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.ROLES TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.ROLES TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ROLES TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.ROLES TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ROLES TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.RESOURCES
(
  RESOURCE_ID NUMBER(16) NOT NULL ,
  RESOURCE_URI VARCHAR2(1000)
, DESCRIPTION VARCHAR2(100) 
, CREATED_BY NUMBER(16)
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT RESOURCE_ID_PK PRIMARY KEY ( RESOURCE_ID  )  ENABLE 

)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.RESOURCES TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.RESOURCES TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.RESOURCES TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.RESOURCES TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.RESOURCES TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE CPS.ROLE_PRIVILEGES
(
  PRIVILEGE_DETAILS_ID NUMBER(16) NOT NULL 
,  RESOURCE_ID NUMBER(16) NOT NULL   
, ROLE_ID NUMBER(16) NOT NULL
, DESCRIPTION VARCHAR2(100) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
,CONSTRAINT ROLE_PRIVILEGES_PK PRIMARY KEY(PRIVILEGE_DETAILS_ID,RESOURCE_ID,ROLE_ID) ENABLE
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;

ALTER TABLE CPS.ROLE_PRIVILEGES ADD CONSTRAINT ROLE_PRIV_PRIV_DETAILS_ID_FK FOREIGN KEY (PRIVILEGE_DETAILS_ID) REFERENCES CPS.PRIVILEGE_DETAILS (PRIVILEGE_DETAILS_ID); 
ALTER TABLE CPS.ROLE_PRIVILEGES ADD CONSTRAINT ROLE_PRIV_ROLE_ID_FK FOREIGN KEY (ROLE_ID) REFERENCES CPS.ROLES (ROLE_ID); 
ALTER TABLE CPS.ROLE_PRIVILEGES ADD CONSTRAINT ROLE_PRIV_RESOURCE_ID_FK FOREIGN KEY (RESOURCE_ID) REFERENCES CPS.RESOURCES (RESOURCE_ID); 
   

GRANT DELETE		ON CPS.ROLE_PRIVILEGES TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.ROLE_PRIVILEGES TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ROLE_PRIVILEGES TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.ROLE_PRIVILEGES TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ROLE_PRIVILEGES TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE CPS.VENDORS 
(
  VENDOR_ID NUMBER(16) NOT NULL 
, VENDOR_NAME VARCHAR2(100) 
, DESCRIPTION VARCHAR2(20) 
, ACCOUNTNUMBER VARCHAR2(20) 
, ADDRESS_ID NUMBER(16)
, RATING NUMBER(1) 
, STATUS NUMBER(1) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, ENTERPRISE_ID NUMBER(16) 
, CONSTRAINT VENDOR_ID_PK PRIMARY KEY   (    VENDOR_ID   )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.VENDORS ADD CONSTRAINT VENDORS_ADDRESS_ID_FK FOREIGN KEY (ADDRESS_ID) REFERENCES CPS.ADDRESS (ADDRESS_ID); 

GRANT DELETE		ON CPS.VENDORS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.VENDORS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.VENDORS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.VENDORS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.VENDORS TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE CPS.PROJECTS 
(
  PROJECT_ID NUMBER(16) NOT NULL 
, PROJECT_NAME VARCHAR2(100) 
, START_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, END_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, STATUS NUMBER(1) 
, PROJECT_CODE VARCHAR2(20) 
, ESTIMATED_COST NUMBER(20,2)
, ACTUAL_COST NUMBER(20,2)
, ESTIMATED_HOURS NUMBER(6,2) 
, ACTUAL_HOURS NUMBER(6,2)
, DESCRIPTION VARCHAR2(1000)  
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT PROJECT_ID_PK PRIMARY KEY   (    PROJECT_ID  )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.PROJECTS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.PROJECTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.PROJECTS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.PROJECTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.PROJECTS TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



CREATE TABLE CPS.USERS 
(
  USER_ID NUMBER(16) 
, FIRST_NAME VARCHAR2(50) 
, LAST_NAME VARCHAR2(50) 
, MI VARCHAR2(20) 
, TELEPHONE VARCHAR2(20) 
, EMAIL VARCHAR2(20) 
, SSN VARCHAR2(20) 
, ADDRESS_ID NUMBER(16) 
, DEPARTMENT_ID NUMBER(16) 
, ENTERPRISE_ID NUMBER(16) 
, VENODR_ID VARCHAR2(20) 
, HOURLY_RATE NUMBER(6,2)
, BEGIN_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, END_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONTRACT_TYPE VARCHAR2(20) 
, CREATED_BY NUMBER(16) 
, CREATE_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP VARCHAR2(20) 
, CONSTRAINT USER_ID_PK PRIMARY KEY   (    USER_ID  )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.USERS ADD CONSTRAINT USERS_ADDRESS_ID_FK FOREIGN KEY (ADDRESS_ID) REFERENCES CPS.ADDRESS (ADDRESS_ID); 
ALTER TABLE CPS.USERS ADD CONSTRAINT USERS_ENTERPRISE_ID_FK FOREIGN KEY (ENTERPRISE_ID) REFERENCES CPS.ENTERPRISE (ENTERPRISE_ID); 
ALTER TABLE CPS.USERS ADD CONSTRAINT USERS_DEPARTMENT_ID_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES CPS.DEPARTMENTS (DEPARTMENT_ID); 


GRANT DELETE		ON CPS.USERS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.USERS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USERS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.USERS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USERS TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.LOGIN_DETAILS
(
  USER_ID NUMBER(16) NOT NULL
, USER_LOGIN_NAME VARCHAR2(20)   
, USER_PASSWORD VARCHAR2(20) 
, ROLE_ID VARCHAR2(20) 
, ADDRESS_ID VARCHAR2(20) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT LOGIN_DETAILS_PK PRIMARY KEY   (    USER_LOGIN_NAME   )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  
ALTER TABLE CPS.LOGIN_DETAILS ADD CONSTRAINT LOGIN_DETAILS_USER_ID_FK FOREIGN KEY (USER_ID) REFERENCES CPS.USERS (USER_ID); 

  GRANT DELETE		ON CPS.LOGIN_DETAILS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.LOGIN_DETAILS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.LOGIN_DETAILS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.LOGIN_DETAILS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.LOGIN_DETAILS TO CPS_RO_ROLE;

  COMMIT;
  --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.USER_ROLES
(
  ROLE_ID NUMBER(16) NOT NULL 
, USER_ID NUMBER(16) NOT NULL
, DESCRIPTION VARCHAR2(100) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT USER_ROLES_PK PRIMARY KEY   (    ROLE_ID,USER_ID   )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;

ALTER TABLE CPS.USER_ROLES ADD CONSTRAINT USER_ROLES_ROLE_ID_FK FOREIGN KEY (ROLE_ID) REFERENCES CPS.ROLES (ROLE_ID); 
ALTER TABLE CPS.USER_ROLES ADD CONSTRAINT USER_ROLES_USER_ID_FK FOREIGN KEY (USER_ID) REFERENCES CPS.USERS (USER_ID); 

GRANT DELETE		ON CPS.USER_ROLES TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.USER_ROLES TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USER_ROLES TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.USER_ROLES TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USER_ROLES TO CPS_RO_ROLE;
COMMIT;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.USER_PROJECTS 
(
  PROJECT_ID NUMBER(16) NOT NULL 
,  USER_ID NUMBER(16) NOT NULL 
,  USER_PROJECT_ROLE VARCHAR2(200) 
, HOURLY_RATE NUMBER(6,2) 
, START_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, END_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, STATUS NUMBER(1) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT USER_PROJECTS_PK PRIMARY KEY   (   PROJECT_ID ,USER_ID   )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.USER_PROJECTS ADD CONSTRAINT USER_PROJECTS_USER_ID_FK FOREIGN KEY (USER_ID) REFERENCES CPS.USERS (USER_ID); 
ALTER TABLE CPS.USER_PROJECTS ADD CONSTRAINT USER_PROJECTS_PROJECT_ID_FK FOREIGN KEY (PROJECT_ID) REFERENCES CPS.PROJECTS (PROJECT_ID); 
  
GRANT DELETE		ON CPS.USER_PROJECTS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.USER_PROJECTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USER_PROJECTS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.USER_PROJECTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USER_PROJECTS TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.VENDOR_PROJECTS 
(
  PROJECT_ID NUMBER(16) NOT NULL 
,  VENDOR_ID NUMBER(16) NOT NULL 
, START_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, END_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, STATUS NUMBER(1) 
, ESTIMATED_COST NUMBER(20,2) 
, ACTUAL_COST NUMBER(20,2)
, ESTIMATED_HOURS NUMBER(6,2) 
, ACTUAL_HOURS NUMBER(6,2)
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT VENDOR_PROJECTS_PK PRIMARY KEY   (   PROJECT_ID ,VENDOR_ID   )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.VENDOR_PROJECTS ADD CONSTRAINT VENDOR_PROJECTS_PROJECT_ID_FK FOREIGN KEY (PROJECT_ID) REFERENCES CPS.PROJECTS (PROJECT_ID); 
ALTER TABLE CPS.VENDOR_PROJECTS ADD CONSTRAINT VENDOR_PROJECTS_VENDOR_ID_FK FOREIGN KEY (VENDOR_ID) REFERENCES CPS.VENDORS (VENDOR_ID); 
  
GRANT DELETE		ON CPS.VENDOR_PROJECTS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.VENDOR_PROJECTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.VENDOR_PROJECTS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.VENDOR_PROJECTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.VENDOR_PROJECTS TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.DEPARTMENT_PROJECTS 
(
  PROJECT_ID NUMBER(16) NOT NULL 
,  DEPARTMENT_ID NUMBER(16) NOT NULL 
, START_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, END_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, STATUS NUMBER(1) 
, ESTIMATED_COST NUMBER(20,2)
, ACTUAL_COST NUMBER(20,2)
, ESTIMATED_HOURS NUMBER(6,2) 
, ACTUAL_HOURS NUMBER(6,2)
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT DEPARTMENT_PROJECTS_PK PRIMARY KEY   (   PROJECT_ID ,DEPARTMENT_ID   )  ENABLE 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.DEPARTMENT_PROJECTS ADD CONSTRAINT DEPT_PROJECTS_DEPT_ID_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES CPS.DEPARTMENTS (DEPARTMENT_ID); 
ALTER TABLE CPS.DEPARTMENT_PROJECTS ADD CONSTRAINT DEPT_PROJECTS_PROJECT_ID_FK FOREIGN KEY (PROJECT_ID) REFERENCES CPS.PROJECTS (PROJECT_ID); 

GRANT DELETE		ON CPS.DEPARTMENT_PROJECTS TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.DEPARTMENT_PROJECTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.DEPARTMENT_PROJECTS TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.DEPARTMENT_PROJECTS TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.DEPARTMENT_PROJECTS TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COMMENT ON COLUMN CPS.ENTERPRISE.STATUS IS '0.active
1.active';

COMMENT ON COLUMN CPS.ENTERPRISE.LICENSE_TYPE IS '0=STAND_ALONE
1=PER 5 USERS
2=PER 10 USERS
3=PER 50 USERS';

COMMENT ON COLUMN CPS.PRIVILEGE_DETAILS.IS_ALLOWED IS 'Y
N';


COMMENT ON COLUMN CPS.PROJECTS.STATUS IS '0. active
1. inactive';

COMMENT ON COLUMN CPS.ROLES.ROLE_ID IS '100-super admin
200-enterprise admin
300-department admin
400-user';

COMMENT ON COLUMN CPS.USERS.CONTRACT_TYPE IS 'sow OR TandM
';

COMMENT ON COLUMN CPS.VENDORS.RATING IS '1 = Superior
2 = Excellent
3 = Above average
4 = Average
5 = Below average';

COMMENT ON COLUMN CPS.VENDORS.STATUS IS '0 = Vendor is no longer used.
1 = Vendor is actively used.';

COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 CREATE TABLE CPS.ADDRESS_AUDIT
  (
    
  ADDRESS_ID NUMBER(16) NOT NULL ,
  COMMENTS VARCHAR2(1000) ,
  CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
,  CONSTRAINT ADDRESS_ID_FK FOREIGN KEY (  ADDRESS_ID  ) REFERENCES CPS.ADDRESS(ADDRESS_ID) 
  )
 /* SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.ADDRESS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.ADDRESS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ADDRESS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.ADDRESS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ADDRESS_AUDIT TO CPS_RO_ROLE;
COMMIT;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.ENTERPRISE_AUDIT
(
  ENTERPRISE_ID NUMBER(16) NOT NULL 
, COMMENTS VARCHAR2(1000)
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT ENTERPRISE_AUDIT_ID_FK FOREIGN KEY   (    ENTERPRISE_ID  )   REFERENCES CPS.ENTERPRISE( ENTERPRISE_ID) 
)
 /* SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.ENTERPRISE_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.ENTERPRISE_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ENTERPRISE_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.ENTERPRISE_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ENTERPRISE_AUDIT TO CPS_RO_ROLE;
COMMIT;

-~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.DEPARTMENTS_AUDIT
(
  DEPARTMENT_ID NUMBER(16) NOT NULL 
, COMMENTS VARCHAR2(1000)
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT DEPARTMENTS_AUDIT_ID_FK  FOREIGN KEY (DEPARTMENT_ID)  REFERENCES CPS.DEPARTMENTS ( DEPARTMENT_ID  )  
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.DEPARTMENTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.DEPARTMENTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.DEPARTMENTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.DEPARTMENTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.DEPARTMENTS_AUDIT TO CPS_RO_ROLE;
COMMIT;
 --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.PRIVILEGE_DETAILS_AUDIT
(
  PRIVILEGE_DETAILS_ID NUMBER(16) NOT NULL 
, COMMENTS VARCHAR2(1000)  
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, IS_ALLOWED CHAR(1)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT PRIVILEGE_DETAILS_AUDIT_ID_FK FOREIGN KEY   (    PRIVILEGE_DETAILS_ID   )  REFERENCES CPS.PRIVILEGE_DETAILS(PRIVILEGE_DETAILS_ID) 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.PRIVILEGE_DETAILS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.PRIVILEGE_DETAILS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.PRIVILEGE_DETAILS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.PRIVILEGE_DETAILS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.PRIVILEGE_DETAILS_AUDIT TO CPS_RO_ROLE;
COMMIT;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.ROLES_AUDIT
(
  ROLE_ID NUMBER(16) NOT NULL 
, COMMENTS VARCHAR2(1000) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT ROLES_AUDIT_ID_FK FOREIGN KEY   (    ROLE_ID  )  REFERENCES CPS.ROLES(ROLE_ID) 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.ROLES_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.ROLES_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ROLES_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.ROLES_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ROLES_AUDIT TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.RESOURCES_AUDIT
(
  RESOURCE_ID NUMBER(16) NOT NULL ,
  COMMENTS VARCHAR2(1000)
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT RESOURCES_AUDIT_ID_FK FOREIGN KEY ( RESOURCE_ID  )  REFERENCES CPS.RESOURCES( RESOURCE_ID) 

)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.RESOURCES_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.RESOURCES_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.RESOURCES_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.RESOURCES_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.RESOURCES_AUDIT TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE CPS.ROLE_PRIVILEGES_AUDIT
(
  PRIVILEGE_DETAILS_ID NUMBER(16) NOT NULL 
,  RESOURCE_ID NUMBER(16) NOT NULL   
, ROLE_ID NUMBER(16) NOT NULL
, COMMENTS VARCHAR2(100) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
,CONSTRAINT ROLE_PRIVILEGES_AUDIT_PK PRIMARY KEY(PRIVILEGE_DETAILS_ID,RESOURCE_ID,ROLE_ID) ENABLE
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;

ALTER TABLE CPS.ROLE_PRIVILEGES_AUDIT ADD CONSTRAINT ROLE_PRIV_AUD_PRIV_DET_ID_FK FOREIGN KEY (PRIVILEGE_DETAILS_ID) REFERENCES CPS.PRIVILEGE_DETAILS (PRIVILEGE_DETAILS_ID); 
ALTER TABLE CPS.ROLE_PRIVILEGES_AUDIT ADD CONSTRAINT ROLE_PRIV_AUD_ROLE_ID_FK FOREIGN KEY (ROLE_ID) REFERENCES CPS.ROLES (ROLE_ID); 
ALTER TABLE CPS.ROLE_PRIVILEGES_AUDIT ADD CONSTRAINT ROLE_PRIV_AUDIT_RESOURCE_ID_FK FOREIGN KEY (RESOURCE_ID) REFERENCES CPS.RESOURCES (RESOURCE_ID); 
   

GRANT DELETE		ON CPS.ROLE_PRIVILEGES_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.ROLE_PRIVILEGES_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ROLE_PRIVILEGES_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.ROLE_PRIVILEGES_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.ROLE_PRIVILEGES_AUDIT TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.VENDORS_AUDIT
(
  VENDOR_ID NUMBER(16) NOT NULL 
, COMMENTS VARCHAR2(1000) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT VENDORS_AUDIT_ID_FK FOREIGN KEY   (    VENDOR_ID   )  REFERENCES CPS.VENDORS(VENDOR_ID )
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.VENDORS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.VENDORS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.VENDORS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.VENDORS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.VENDORS_AUDIT TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.PROJECTS_AUDIT
(
  PROJECT_ID NUMBER(16) NOT NULL 
, COMMENTS VARCHAR2(1000) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT PROJECTS_AUDIT_ID_FK FOREIGN KEY   (    PROJECT_ID  )  REFERENCES CPS.PROJECTS (PROJECT_ID) 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
GRANT DELETE		ON CPS.PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.PROJECTS_AUDIT TO CPS_RO_ROLE;
COMMIT;


--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.USERS_AUDIT
(
  USER_ID NUMBER(16) 
, COMMENTS VARCHAR2(1000) 
, CREATED_BY NUMBER(16) 
, CREATE_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP VARCHAR2(20) 
, CONSTRAINT USERS_AUDIT_ID_FK FOREIGN KEY   (    USER_ID  ) REFERENCES CPS.USERS(USER_ID) 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  

GRANT DELETE		ON CPS.USERS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.USERS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USERS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.USERS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USERS_AUDIT TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE TABLE CPS.LOGIN_DETAILS_AUDIT
(
  USER_ID NUMBER(16) NOT NULL
, COMMENTS VARCHAR2(1000)   
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, CONSTRAINT LOGIN_DETAILS_AUDIT_FK FOREIGN KEY   (    USER_ID   )  REFERENCES CPS.USERS(USER_ID) 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  


GRANT DELETE		ON CPS.LOGIN_DETAILS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.LOGIN_DETAILS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.LOGIN_DETAILS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.LOGIN_DETAILS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.LOGIN_DETAILS_AUDIT TO CPS_RO_ROLE;

  COMMIT;
  --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.USER_ROLES_AUDIT
(
  ROLE_ID NUMBER(16) NOT NULL 
, USER_ID NUMBER(16) NOT NULL
, COMMENTS VARCHAR2(1000) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;

ALTER TABLE CPS.USER_ROLES_AUDIT ADD CONSTRAINT USER_ROLES_AUDIT_ROLE_ID_FK FOREIGN KEY (ROLE_ID) REFERENCES CPS.ROLES (ROLE_ID); 
ALTER TABLE CPS.USER_ROLES_AUDIT ADD CONSTRAINT USER_ROLES_AUDIT_USER_ID_FK FOREIGN KEY (USER_ID) REFERENCES CPS.USERS (USER_ID); 

GRANT DELETE		ON CPS.USER_ROLES_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.USER_ROLES_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USER_ROLES_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.USER_ROLES_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USER_ROLES_AUDIT TO CPS_RO_ROLE;
COMMIT;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.USER_PROJECTS_AUDIT
(
  PROJECT_ID NUMBER(16) NOT NULL 
,  USER_ID NUMBER(16) NOT NULL 
,  COMMENTS VARCHAR2(1000) 
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.USER_PROJECTS_AUDIT ADD CONSTRAINT USER_PROJECTS_AUDIT_USER_ID_FK FOREIGN KEY (USER_ID) REFERENCES CPS.USERS (USER_ID); 
ALTER TABLE CPS.USER_PROJECTS_AUDIT ADD CONSTRAINT USER_PROJECTS_AUDIT_PROJ_ID_FK FOREIGN KEY (PROJECT_ID) REFERENCES CPS.PROJECTS (PROJECT_ID); 
  
GRANT DELETE		ON CPS.USER_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.USER_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USER_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.USER_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.USER_PROJECTS_AUDIT TO CPS_RO_ROLE;
COMMIT;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.VENDOR_PROJECTS_AUDIT
(
  PROJECT_ID NUMBER(16) NOT NULL 
,  VENDOR_ID NUMBER(16) NOT NULL 
,  COMMENTS VARCHAR2(1000)
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.VENDOR_PROJECTS_AUDIT ADD CONSTRAINT VENDOR_PROJ_AUDIT_PROJ_ID_FK FOREIGN KEY (PROJECT_ID) REFERENCES CPS.PROJECTS (PROJECT_ID); 
ALTER TABLE CPS.VENDOR_PROJECTS_AUDIT ADD CONSTRAINT VENDOR_PROJ_AUDIT_VENDOR_ID_FK FOREIGN KEY (VENDOR_ID) REFERENCES CPS.VENDORS (VENDOR_ID); 
  
GRANT DELETE		ON CPS.VENDOR_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.VENDOR_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.VENDOR_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.VENDOR_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.VENDOR_PROJECTS_AUDIT TO CPS_RO_ROLE;
COMMIT;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE CPS.DEPARTMENT_PROJECTS_AUDIT
(
  PROJECT_ID NUMBER(16) NOT NULL 
,  DEPARTMENT_ID NUMBER(16) NOT NULL 
, COMMENTS VARCHAR2(1000)
, CREATED_BY NUMBER(16) 
, CREATED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 
, MODIFIED_BY NUMBER(16)
, MODIFIED_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP 

)
  /*SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )*/
  TABLESPACE "CPS_DATA" ;
  COMMIT;
  
ALTER TABLE CPS.DEPARTMENT_PROJECTS_AUDIT ADD CONSTRAINT DEPT_PROJ_AUDIT_DEPT_ID_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES CPS.DEPARTMENTS (DEPARTMENT_ID); 
ALTER TABLE CPS.DEPARTMENT_PROJECTS_AUDIT ADD CONSTRAINT DEPT_PROJ_AUDIT_PROJ_ID_FK FOREIGN KEY (PROJECT_ID) REFERENCES CPS.PROJECTS (PROJECT_ID); 

GRANT DELETE		ON CPS.DEPARTMENT_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT INSERT		ON CPS.DEPARTMENT_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.DEPARTMENT_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT UPDATE		ON CPS.DEPARTMENT_PROJECTS_AUDIT TO CPS_UPDATE_ROLE;
GRANT SELECT		ON CPS.DEPARTMENT_PROJECTS_AUDIT TO CPS_RO_ROLE;
COMMIT;

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COMMENT ON COLUMN CPS.ENTERPRISE.STATUS IS '0.active
1.active';

COMMENT ON COLUMN CPS.ENTERPRISE.LICENSE_TYPE IS '0=STAND_ALONE
1=PER 5 USERS
2=PER 10 USERS
3=PER 50 USERS';

COMMENT ON COLUMN CPS.PRIVILEGE_DETAILS.IS_ALLOWED IS 'Y
N';


COMMENT ON COLUMN CPS.PROJECTS.STATUS IS '0. active
1. inactive';

COMMENT ON COLUMN CPS.ROLES.ROLE_ID IS '100-super admin
200-enterprise admin
300-department admin
400-user';

COMMENT ON COLUMN CPS.USERS.CONTRACT_TYPE IS 'sow OR TandM
';

COMMENT ON COLUMN CPS.VENDORS.RATING IS '1 = Superior
2 = Excellent
3 = Above average
4 = Average
5 = Below average';

COMMENT ON COLUMN CPS.VENDORS.STATUS IS '0 = Vendor is no longer used.
1 = Vendor is actively used.';

COMMIT;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~





